package com.vehiclemanagement.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.vehiclemanagement.entity.VehicleInspection;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.math.BigDecimal;
import java.time.LocalDateTime;
import java.util.List;
import java.util.Map;

/**
 * 车辆年检记录Mapper接口
 */
@Mapper
public interface VehicleInspectionMapper extends BaseMapper<VehicleInspection> {

    /**
     * 自定义分页查询，包含车辆信息
     *
     * @param page     分页对象
     * @param whereSql 自定义WHERE条件
     * @return 分页结果
     */
    @Select("SELECT i.*, v.plate_number, " +
            "CASE i.inspection_type " +
            "   WHEN 1 THEN '定期检查' " +
            "   WHEN 2 THEN '年检' " +
            "   WHEN 3 THEN '环保检测' " +
            "   WHEN 4 THEN '综合检测' " +
            "   ELSE '未知类型' " +
            "END AS inspection_type_name, " +
            "CASE i.inspection_result " +
            "   WHEN 1 THEN '通过' " +
            "   WHEN 2 THEN '不通过' " +
            "   ELSE '未知结果' " +
            "END AS inspection_result_name, " +
            "CASE i.status " +
            "   WHEN 0 THEN '未开始' " +
            "   WHEN 1 THEN '进行中' " +
            "   WHEN 2 THEN '已完成' " +
            "   WHEN 3 THEN '已取消' " +
            "   ELSE '未知状态' " +
            "END AS status_name " +
            "FROM vehicle_inspection i " +
            "LEFT JOIN vehicle v ON i.vehicle_id = v.id " +
            "WHERE ${whereSql} " +
            "ORDER BY i.create_time DESC")
    IPage<VehicleInspection> selectPageWithInfo(Page<VehicleInspection> page, @Param("whereSql") String whereSql);

    /**
     * 获取指定车辆的年检总费用
     *
     * @param vehicleId 车辆ID
     * @return 年检总费用
     */
    @Select("SELECT COALESCE(SUM(inspection_cost), 0) FROM vehicle_inspection WHERE vehicle_id = #{vehicleId} AND status = 2 AND del_flag = 0")
    BigDecimal sumInspectionCostByVehicleId(@Param("vehicleId") Long vehicleId);

    /**
     * 获取指定车辆的年检次数
     *
     * @param vehicleId 车辆ID
     * @return 年检次数
     */
    @Select("SELECT COUNT(*) FROM vehicle_inspection WHERE vehicle_id = #{vehicleId} AND status = 2 AND del_flag = 0")
    Integer countInspectionByVehicleId(@Param("vehicleId") Long vehicleId);

    /**
     * 按年检类型统计
     *
     * @param startTime 开始时间
     * @param endTime   结束时间
     * @return 按类型统计的结果
     */
    @Select("SELECT inspection_type, " +
            "COUNT(*) AS count, " +
            "SUM(inspection_cost) AS total_cost " +
            "FROM vehicle_inspection " +
            "WHERE inspection_date BETWEEN #{startTime} AND #{endTime} " +
            "AND status = 2 AND del_flag = 0 " +
            "GROUP BY inspection_type")
    List<Map<String, Object>> statisticsByType(@Param("startTime") LocalDateTime startTime, @Param("endTime") LocalDateTime endTime);

    /**
     * 按月份统计年检费用
     *
     * @param year 年份
     * @return 月度统计结果
     */
    @Select("SELECT DATE_FORMAT(inspection_date, '%Y-%m') AS month, " +
            "COUNT(*) AS count, " +
            "SUM(inspection_cost) AS total_cost " +
            "FROM vehicle_inspection " +
            "WHERE YEAR(inspection_date) = #{year} " +
            "AND status = 2 AND del_flag = 0 " +
            "GROUP BY DATE_FORMAT(inspection_date, '%Y-%m') " +
            "ORDER BY month")
    List<Map<String, Object>> statisticsByMonth(@Param("year") Integer year);
    
    /**
     * 查询即将到期需要年检的车辆
     *
     * @param daysThreshold 天数阈值（距离有效期结束的天数）
     * @return 需要年检的车辆列表
     */
    @Select("SELECT v.id, v.plate_number, i.valid_end_date, " +
            "DATEDIFF(i.valid_end_date, NOW()) AS days_remaining " +
            "FROM vehicle v " +
            "JOIN (" +
            "   SELECT vehicle_id, MAX(valid_end_date) AS valid_end_date " +
            "   FROM vehicle_inspection " +
            "   WHERE status = 2 AND inspection_result = 1 AND del_flag = 0 " +
            "   GROUP BY vehicle_id" +
            ") i ON v.id = i.vehicle_id " +
            "WHERE DATEDIFF(i.valid_end_date, NOW()) <= #{daysThreshold} " +
            "AND DATEDIFF(i.valid_end_date, NOW()) >= 0 " +
            "ORDER BY days_remaining")
    List<Map<String, Object>> findVehiclesNeedingInspection(@Param("daysThreshold") Integer daysThreshold);

    /**
     * 物理删除记录
     *
     * @param id 记录ID
     * @return 影响的行数
     */
    @Delete("DELETE FROM vehicle_inspection WHERE id = #{id}")
    int deletePhysically(@Param("id") Long id);
} 